using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using Framework.Library.Core.Config;
using Framework.Library.Core.Data.DataManager;
using Framework.Library.Core.Utils.Converters;
using Framework.Library.Core.Utils.Database;

namespace Framework.Library.Core.Data.DataProvider
{
    public class MSSQLDBManager : DBManager
    {
        private SqlCommand idbCommand;
        private SqlConnection idbConnection;
        private SqlTransaction idbTransaction;

        public string ConnectionString
        {
            get { return Configurations.CONNECTIONSTRING; }
        }

        public SqlConnection Connection
        {
            get { return idbConnection; }
        }

        public SqlTransaction Transaction
        {
            get { return idbTransaction; }
        }

        public SqlCommand Command
        {
            get { return idbCommand; }
        }

        #region DBManager Members

        public bool CheckConnection()
        {
            bool isReadyToConnect = true;
            var dbConnection = new SqlConnection();
            try
            {
                if (idbConnection.State != ConnectionState.Open)
                {
                    idbConnection.ConnectionString = ConnectionString;
                    idbConnection.Open();
                }
            }
            catch (TimeoutException)
            {
                isReadyToConnect = false;
            }
            catch (Exception ex)
            {
#if DEBUG
                var exception2 = new Exception("[CheckConnection] caused exception: " + ex.Message);
#else
                Exception exception2 = new Exception("Error found !");
#endif
                throw exception2;
            }
            finally
            {
                if (dbConnection.State == ConnectionState.Open)
                {
                    dbConnection.Close();
                }
            }
            return isReadyToConnect;
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText, ArrayList parameters, bool transaction)
        {
            int returnValue = 0;
            try
            {
                Open();
                PrepareCommand(commandType, commandText, parameters);
                BeginTransaction(transaction);                
                returnValue = Command.ExecuteNonQuery();
                if (Command.Parameters.Count > 0)
                {
                    foreach (SqlParameter parameter in Command.Parameters)
                    {
                        if (parameter.Direction != null)
                        {
                            if (parameter.Direction == ParameterDirection.Output)
                                int.TryParse(parameter.Value.ToString(), out returnValue);
                        }
                    }
                }
                CommitTransaction(transaction);
            }
            catch (Exception ex)
            {
#if DEBUG
                var exception2 = new Exception("[" + commandText + "] caused exception: " + ex.Message);
#else
                Exception exception2 = new Exception("Error found !");
#endif
                throw exception2;
            }
            finally
            {
                Command.Parameters.Clear();
                Dispose();
            }
            return returnValue;
        }

        public object ExecuteScalar(CommandType commandType, string commandText, ArrayList parameters, bool transaction)
        {
            object returnValue = null;
            try
            {
                Open();
                PrepareCommand(commandType, commandText, parameters);
                BeginTransaction(transaction);
                returnValue = Command.ExecuteScalar();
                CommitTransaction(transaction);
            }
            catch (Exception ex)
            {
#if DEBUG
                var exception2 = new Exception("[" + commandText + "] caused exception: " + ex.Message);
#else
                Exception exception2 = new Exception("Error found !");
#endif
                throw exception2;
            }
            finally
            {
                Command.Parameters.Clear();
                Dispose();
            }
            return returnValue;
        }

        public DataSet ExecuteDataSet(CommandType commandType, string commandText, ArrayList parameters,
                                      bool transaction)
        {
            var returnValue = new DataSet();
            try
            {
                Open();
                PrepareCommand(commandType, commandText, parameters);
                var adapter = new SqlDataAdapter(Command);
                BeginTransaction(transaction);
                adapter.Fill(returnValue);
                CommitTransaction(transaction);
            }
            catch (Exception ex)
            {
#if DEBUG
                var exception2 = new Exception("[" + commandText + "] caused exception: " + ex.Message);
#else
                Exception exception2 = new Exception("Error found !");
#endif
                throw exception2;
            }
            finally
            {
                Command.Parameters.Clear();
                Dispose();
            }
            return returnValue;
        }

        public List<T> ExecuteList<T>(CommandType commandType, string commandText, ArrayList parameters,
                                      bool transaction)
        {
            var m_List = new List<T>();
            Open();
            PrepareCommand(commandType, commandText, parameters);
            BeginTransaction(transaction);
            try
            { 
                SqlDataReader returnValue = Command.ExecuteReader();
                if (returnValue == null)
                    return null;
                while (returnValue.Read())
                {
                    object objTarget = Activator.CreateInstance(typeof(T));
                    PropertyInfo[] objProperties = typeof(T).GetProperties();
                    for (int i = 0; i < returnValue.FieldCount; i++)
                    {
                        for (int k = 0; k < objProperties.Length; k++)
                        {
                            PropertyInfo property = objProperties[k];
                            if (returnValue.GetName(i).Equals(property.Name,
                                                              StringComparison.InvariantCultureIgnoreCase))
                            {
                                if (property.CanWrite)
                                {
                                    if (returnValue[property.Name] != null)
                                    {
                                        property.SetValue(objTarget,
                                                          Convert.ChangeType(
                                                              returnValue[property.Name.ToUpper()],
                                                              property.PropertyType), null);
                                    }
                                    else
                                    {
                                        property.SetValue(objTarget, string.Empty, null);
                                    }
                                }
                            }
                        }
                    }
                    m_List.Add((T)objTarget);
                }
                if (!returnValue.IsClosed)
                    returnValue.Close();
                idbCommand.Parameters.Clear();
                CommitTransaction(transaction);
            }
            catch (Exception ex)
            {
#if DEBUG
                var exception2 = new Exception("[" + commandText + "] caused exception: " + ex.Message);
#else
                Exception exception2 = new Exception("Error found !");
#endif
                throw exception2;
            }
            finally
            {
                Command.Parameters.Clear();
                Dispose();
            }
            if (m_List.Count > 0)
                return m_List;
            return null;
        }

        public List<T> ExecuteList<T>(CommandType commandType, string commandText, ArrayList parameters,
                                      bool transaction, out int Total)
        {
            SqlDataReader returnValue = null;
            var m_List = new List<T>();
            Total = 0;
            Open();
            PrepareCommand(commandType, commandText, parameters);
            try
            {
                
                //BeginTransaction(transaction);
                //SqlDataReader dr = idbCommand.ExecuteReader();
                //if (dr == null || dr.FieldCount == 0)
                //    return null;
                //var builder = DynamicBuilder<T>.CreateBuilder(dr);
                //while (dr.Read())
                //{
                //    var r = builder.Build(dr);
                //    m_List.Add(r);
                //}
                //if (!dr.IsClosed)
                //    dr.Close();
                //CommitTransaction(transaction);
                using (returnValue = idbCommand.ExecuteReader())
                {
                    object objTarget = null;
                    while (returnValue.Read())
                    {
                        objTarget = Activator.CreateInstance(typeof(T));
                        PropertyInfo[] objProperties = typeof(T).GetProperties();
                        for (int i = 0; i < returnValue.FieldCount; i++)
                        {
                            for (int k = 0; k < objProperties.Length; k++)
                            {
                                PropertyInfo property = objProperties[k];
                                if (returnValue.GetName(i).Equals(property.Name,
                                                                  StringComparison.InvariantCultureIgnoreCase))
                                {
                                    if (property.CanWrite)
                                    {
                                        if (returnValue[property.Name] != null)
                                        {
                                            property.SetValue(objTarget,
                                                              Convert.ChangeType(
                                                                  returnValue[property.Name.ToUpper()],
                                                                  property.PropertyType), null);
                                        }
                                        else
                                        {
                                            property.SetValue(objTarget, "", null);
                                        }
                                    }
                                }
                            }
                        }
                        m_List.Add((T)objTarget);
                    }
                    if (returnValue.NextResult())
                    {
                        if (returnValue.Read())
                        {
                            Total = (int)returnValue["TOTAL"];
                        }
                    }
                }
            }
            catch (Exception ex)
            {
#if DEBUG
                var exception2 = new Exception("[" + commandText + "] caused exception: " + ex.Message);
#else
                Exception exception2 = new Exception("Error found !");
#endif
                throw exception2;
            }
            finally
            {
                idbCommand.Parameters.Clear();
                Dispose();
            }
            if (m_List.Count > 0)
                return m_List;
            return null;
        }

        #endregion

        private void BeginTransaction(bool transaction)
        {
            if (transaction)
            {
                if (idbTransaction == null)
                    idbTransaction = Connection.BeginTransaction(IsolationLevel.ReadUncommitted);
                idbCommand.Transaction = idbTransaction;
            }
        }

        private void CommitTransaction(bool transaction)
        {
            if (transaction)
            {
                if (idbTransaction != null)
                    idbTransaction.Commit();
                idbTransaction = null;
            }
        }

        private void Open()
        {
            idbConnection = new SqlConnection();
            try
            {
                if (idbConnection.State != ConnectionState.Open)
                {
                    idbConnection.ConnectionString = ConnectionString;
                    idbConnection.Open();
                    idbCommand = new SqlCommand();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void Close()
        {
            try
            {
                if (idbConnection.State != ConnectionState.Closed)
                    idbConnection.Close();
            }
            catch (Exception)
            {
                idbCommand = null;
                idbTransaction = null;
                idbConnection = null;
            }
        }

        private void Dispose()
        {
            GC.SuppressFinalize(this);
            Close();
        }

        private void AttachParameters(ArrayList parameters)
        {
            if (parameters.Count > 0)
            {
                for (int i = 0; i < parameters.Count; i++)
                {
                    var arr = (object[]) parameters[i];
                    var p_name = new SqlParameter(arr[0].ToString().Trim(), (arr[1] == null) ? DBNull.Value : arr[1]);
                    if (arr.Length > 2)
                    {
                        if (arr[2] != null)
                        {
                            var dbType = (DbType) arr[2];
                            p_name.DbType = dbType;
                        }
                        if (arr.Length > 3)
                        {
                            if (arr[3] != null)
                            {
                                var dbDirection = (ParameterDirection) arr[3];
                                p_name.Direction = dbDirection;
                            }
                        }
                    }
                    idbCommand.Parameters.Add(p_name);
                }
            }
        }


        private void PrepareCommand(CommandType commandType, string commandText, ArrayList parameters)
        {
            idbCommand.Connection = Connection;
            idbCommand.CommandText = commandText;
            idbCommand.CommandType = commandType;
            if (parameters != null)
            {
                AttachParameters(parameters);
            }
        }
    }
}